package com.example.repository;

import com.example.model.MerchantService;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * 商家服务Repository
 */
@Repository
public interface MerchantServiceRepository extends JpaRepository<MerchantService, Long> {

    /**
     * 根据商家ID查找服务
     */
    List<MerchantService> findByMerchantIdOrderByCreatedAtDesc(String merchantId);

    /**
     * 分页根据商家ID查找服务
     */
    Page<MerchantService> findByMerchantIdOrderByCreatedAtDesc(String merchantId, Pageable pageable);

    /**
     * 根据状态查找服务
     */
    List<MerchantService> findByStatusOrderBySortOrderDescCreatedAtDesc(MerchantService.ServiceStatus status);

    /**
     * 分页根据状态查找服务
     */
    Page<MerchantService> findByStatusOrderBySortOrderDescCreatedAtDesc(MerchantService.ServiceStatus status, Pageable pageable);

    /**
     * 根据分类查找已发布的服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.category = :category " +
           "ORDER BY ms.isRecommended DESC, ms.isPopular DESC, ms.sortOrder DESC, ms.createdAt DESC")
    List<MerchantService> findPublishedServicesByCategory(@Param("category") MerchantService.ServiceCategory category);

    /**
     * 分页根据分类查找已发布的服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.category = :category " +
           "ORDER BY ms.isRecommended DESC, ms.isPopular DESC, ms.sortOrder DESC, ms.createdAt DESC")
    Page<MerchantService> findPublishedServicesByCategory(@Param("category") MerchantService.ServiceCategory category, Pageable pageable);

    /**
     * 查找推荐服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.isRecommended = true " +
           "ORDER BY ms.sortOrder DESC, ms.rating DESC, ms.createdAt DESC")
    List<MerchantService> findRecommendedServices();

    /**
     * 分页查找推荐服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.isRecommended = true " +
           "ORDER BY ms.sortOrder DESC, ms.rating DESC, ms.createdAt DESC")
    Page<MerchantService> findRecommendedServices(Pageable pageable);

    /**
     * 查找热门服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.isPopular = true " +
           "ORDER BY ms.orderCount DESC, ms.rating DESC, ms.createdAt DESC")
    List<MerchantService> findPopularServices();

    /**
     * 分页查找热门服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.isPopular = true " +
           "ORDER BY ms.orderCount DESC, ms.rating DESC, ms.createdAt DESC")
    Page<MerchantService> findPopularServices(Pageable pageable);

    /**
     * 根据价格范围查找服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "AND ms.price BETWEEN :minPrice AND :maxPrice " +
           "ORDER BY ms.price ASC")
    List<MerchantService> findServicesByPriceRange(@Param("minPrice") BigDecimal minPrice, 
                                                  @Param("maxPrice") BigDecimal maxPrice);

    /**
     * 分页根据价格范围查找服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "AND ms.price BETWEEN :minPrice AND :maxPrice " +
           "ORDER BY ms.price ASC")
    Page<MerchantService> findServicesByPriceRange(@Param("minPrice") BigDecimal minPrice, 
                                                  @Param("maxPrice") BigDecimal maxPrice, 
                                                  Pageable pageable);

    /**
     * 搜索服务（标题或描述包含关键词）
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND " +
           "(LOWER(ms.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(ms.description) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "ORDER BY ms.isRecommended DESC, ms.rating DESC, ms.createdAt DESC")
    List<MerchantService> searchServices(@Param("keyword") String keyword);

    /**
     * 分页搜索服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND " +
           "(LOWER(ms.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(ms.description) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "ORDER BY ms.isRecommended DESC, ms.rating DESC, ms.createdAt DESC")
    Page<MerchantService> searchServices(@Param("keyword") String keyword, Pageable pageable);

    /**
     * 根据服务区域查找服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "AND LOWER(ms.serviceArea) LIKE LOWER(CONCAT('%', :area, '%')) " +
           "ORDER BY ms.isRecommended DESC, ms.rating DESC, ms.createdAt DESC")
    List<MerchantService> findServicesByArea(@Param("area") String area);

    /**
     * 分页根据服务区域查找服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "AND LOWER(ms.serviceArea) LIKE LOWER(CONCAT('%', :area, '%')) " +
           "ORDER BY ms.isRecommended DESC, ms.rating DESC, ms.createdAt DESC")
    Page<MerchantService> findServicesByArea(@Param("area") String area, Pageable pageable);

    /**
     * 根据评分范围查找服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "AND ms.rating >= :minRating " +
           "ORDER BY ms.rating DESC, ms.reviewCount DESC")
    List<MerchantService> findServicesByRating(@Param("minRating") BigDecimal minRating);

    /**
     * 增加浏览次数
     */
    @Modifying
    @Query("UPDATE MerchantService ms SET ms.viewCount = ms.viewCount + 1 WHERE ms.id = :id")
    void incrementViewCount(@Param("id") Long id);

    /**
     * 增加订单次数
     */
    @Modifying
    @Query("UPDATE MerchantService ms SET ms.orderCount = ms.orderCount + 1 WHERE ms.id = :id")
    void incrementOrderCount(@Param("id") Long id);

    /**
     * 增加收藏次数
     */
    @Modifying
    @Query("UPDATE MerchantService ms SET ms.favoriteCount = ms.favoriteCount + 1 WHERE ms.id = :id")
    void incrementFavoriteCount(@Param("id") Long id);

    /**
     * 减少收藏次数
     */
    @Modifying
    @Query("UPDATE MerchantService ms SET ms.favoriteCount = GREATEST(0, ms.favoriteCount - 1) WHERE ms.id = :id")
    void decrementFavoriteCount(@Param("id") Long id);

    /**
     * 更新评分和评价数量
     */
    @Modifying
    @Query("UPDATE MerchantService ms SET ms.rating = :rating, ms.reviewCount = :reviewCount WHERE ms.id = :id")
    void updateRatingAndReviewCount(@Param("id") Long id, @Param("rating") BigDecimal rating, @Param("reviewCount") Integer reviewCount);

    /**
     * 统计各分类的服务数量
     */
    @Query("SELECT ms.category, COUNT(ms) FROM MerchantService ms WHERE ms.status = 'PUBLISHED' GROUP BY ms.category")
    List<Object[]> countServicesByCategory();

    /**
     * 统计各状态的服务数量
     */
    @Query("SELECT ms.status, COUNT(ms) FROM MerchantService ms GROUP BY ms.status")
    List<Object[]> countServicesByStatus();

    /**
     * 统计商家的服务数量
     */
    @Query("SELECT COUNT(ms) FROM MerchantService ms WHERE ms.merchantId = :merchantId AND ms.status = 'PUBLISHED'")
    Long countPublishedServicesByMerchant(@Param("merchantId") String merchantId);

    /**
     * 查找最新发布的服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "ORDER BY ms.publishedAt DESC")
    List<MerchantService> findLatestPublishedServices(Pageable pageable);

    /**
     * 查找最受欢迎的服务（按订单数量排序）
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' " +
           "ORDER BY ms.orderCount DESC, ms.rating DESC")
    List<MerchantService> findMostOrderedServices(Pageable pageable);

    /**
     * 查找评分最高的服务
     */
    @Query("SELECT ms FROM MerchantService ms WHERE ms.status = 'PUBLISHED' AND ms.reviewCount >= :minReviews " +
           "ORDER BY ms.rating DESC, ms.reviewCount DESC")
    List<MerchantService> findTopRatedServices(@Param("minReviews") Integer minReviews, Pageable pageable);
}
